import pymysql

class DBManager:
    def __init__(self, host, user, password, database):
        """
        初始化数据库连接
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.conn = None

    def connect(self):
        """
        连接数据库
        """
        self.conn = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

    def disconnect(self):
        """
        断开数据库连接
        """
        if self.conn:
            self.conn.close()

    def execute(self, sql, params=None):
        """
        执行 SQL 语句
        """
        try:
            if not self.conn:
                self.connect()
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params)
                self.conn.commit()
            return True
        except Exception as e:
            print(f"执行 SQL 时出错: {e}")
            return False
        finally:
            self.disconnect()

    def fetchone(self, sql, params=None):
        """
        查询单条数据
        """
        try:
            if not self.conn:
                self.connect()
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params)
                result = cursor.fetchone()
            return result
        except Exception as e:
            print(f"查询单条数据时出错: {e}")
            return None
        finally:
            self.disconnect()

    def fetchall(self, sql, params=None):
        """
        查询多条数据
        """
        try:
            if not self.conn:
                self.connect()
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params)
                results = cursor.fetchall()
            return results
        except Exception as e:
            print(f"查询多条数据时出错: {e}")
            return []
        finally:
            self.disconnect()

    # 封装 CRUD 操作

    def insert(self, table, data):
        """
        插入数据
        :param table: 表名
        :param data: 字典格式，键为字段名，值为对应的数据
        """
        columns = ', '.join(data.keys())
        values = ', '.join([f"%({key})s" for key in data.keys()])
        sql = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        return self.execute(sql, data)

    def update(self, table, data, conditions):
        """
        更新数据
        :param table: 表名
        :param data: 字典格式，键为字段名，值为对应的数据
        :param conditions: 字典格式，键为字段名，值为对应的条件
        """
        set_clause = ', '.join([f"{key} = %({key})s" for key in data.keys()])
        where_clause = ' AND '.join([f"{key} = %({key})s" for key in conditions.keys()])
        data.update(conditions)
        sql = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
        return self.execute(sql, data)

    def delete(self, table, conditions):
        """
        删除数据
        :param table: 表名
        :param conditions: 字典格式，键为字段名，值为对应的条件
        """
        where_clause = ' AND '.join([f"{key} = %({key})s" for key in conditions.keys()])
        sql = f"DELETE FROM {table} WHERE {where_clause}"
        return self.execute(sql, conditions)

    def get_count(self, table, conditions=None):
        """
        获取数据总数
        :param table: 表名
        :param conditions: 字典格式，键为字段名，值为对应的条件
        """
        where_clause = ''
        params = {}
        if conditions:
            where_clause = ' WHERE ' + ' AND '.join([f"{key} = %({key})s" for key in conditions.keys()])
            params = conditions
        sql = f"SELECT COUNT(*) as count FROM {table}{where_clause}"
        result = self.fetchone(sql, params)
        return result['count'] if result else 0

# # 示例用法
# if __name__ == "__main__":
#     # 数据库配置
#     db_config = {
#         'host': 'localhost',
#         'user': 'root',
#         'password': 'password',
#         'database': 'test_db'
#     }

#     # 初始化数据库管理器
#     db = DBManager(**db_config)

#     # 插入数据
#     insert_data = db.insert(
#         table="users",
#         data={"name": "Alice", "age": 25, "email": "alice@example.com"}
#     )
#     print("插入数据成功:", insert_data)

#     # 更新数据
#     update_data = db.update(
#         table="users",
#         data={"age": 26},
#         conditions={"name": "Alice"}
#     )
#     print("更新数据成功:", update_data)

#     # 查询单条数据
#     user = db.fetchone(
#         "SELECT * FROM users WHERE name = %s",
#         ("Alice",)
#     )
#     print("查询单条数据:", user)

#     # 查询多条数据
#     users = db.fetchall("SELECT * FROM users")
#     print("查询多条数据:", users)

#     # 删除数据
#     delete_data = db.delete(
#         table="users",
#         conditions={"name": "Alice"}
#     )
#     print("删除数据成功:", delete_data)

#     # 获取数据总数
#     count = db.get_count("users", {"age": 25})
#     print("数据总数:", count)